package com.mes.report.service;

import com.yy.base.common.dao.Dao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class FZProductionPageService {
	@Autowired
	private Dao dao;

	
	//获取看板数据
	public Map<String,Object> getReportPageData(){
		Map<String,Object> dataMap = new HashMap<String, Object>(); 
		
		//飞织产线生产看板
		List<Map<String, Object>> fzjKBList = getFZJproductList();
		//异常情况汇总
		List<Map<String, Object>> exceptionList = getExceptionData();
		//飞织机状态
		List<Map<String, Object>> fzjStatusList = getFZJstatus();
		//待生产工单
        List<Map<String, Object>> dJobCodeList = getDJobCodeList();
        //线边库库存
        List<Map<String, Object>> productKcQty = getProductKcQty();
		dataMap.put("fzjKBList", fzjKBList);//飞织产线生产看板
		dataMap.put("exceptionList", exceptionList);//异常情况汇总
		dataMap.put("fzjStatusList", fzjStatusList);//飞织机状态
        dataMap.put("dJobCodeList", dJobCodeList);//待生产工单
        dataMap.put("productKcQty", productKcQty);//线边库库存

        return dataMap;
	}

	/**
	 * 获取飞织产线生产工单信息
	 * @return
	 */
	public List<Map<String, Object>> getFZJproductList(){
		String sql = "select a.fzjcode, a.jobnum, b.code jobcodeing,b.efid,b.exe_date_from starttimes,b.exe_date_from ytime,(round(b.jd)) jd from " +
				"(select ef.name fzjcode,ef.eam_facilities_id efid,count(job.code) jobnum " +
				"from eam_facilities ef " +
				"left join emeso_job_order_exe exe on ef.eam_facilities_id = exe.eam_facilities_id " +
				"LEFT join emeso_job_order job on job.emeso_job_order_id = exe.emeso_job_order_id " +
				"where ef.facility_type='FZJ' " +
//				"and job.status='FINISHED' " +
				"GROUP BY ef.name,ef.eam_facilities_id ORDER BY ef.name) a " +
				"left join (select job.code ,ef.eam_facilities_id efid,ep.process_time,exe.exe_date_from,(ep.process_time/(date_part('day', (current_timestamp - exe.exe_date_from)) * 24 * 60 + date_part('minute', (current_timestamp - exe.exe_date_from))) * 100) jd " +
				"from emeso_job_order job,emeso_job_order_exe exe,eam_facilities ef,Emes_Process ep " +
				"where job.emeso_job_order_id = exe.emeso_job_order_id and ef.eam_facilities_id = exe.eam_facilities_id and ep.emes_process_id = job.emes_process_id and ef.facility_type='FZJ' and job.status='OK')b on a.efid=b.efid";
		return dao.query(sql);
	}
	/**
	 * 获取异常信息
	 * @return
	 */
	public List<Map<String, Object>> getExceptionData(){
		String sql = "select eam.name fzjcode,log.type,log.time intime,log.result,log.status status,ee.name ycname " +
				"from emeso_exception_log log,eam_facilities eam,emes_exception ee " +
				"where log.eam_facilities_id = eam.eam_facilities_id " +
				"and ee.emes_exception_id = log.emes_exception_id " +
				"and eam.facility_type='FZJ' order by eam.name";
		return dao.query(sql);



	}

	/**
	 * 获取飞织状态信息
	 * @return
	 */
	public List<Map<String, Object>> getFZJstatus(){
		String sql = "select eam.name,eam.status " +
				"from eam_facilities eam " +
				"where eam.facility_type='FZJ' " +
				"GROUP BY eam.name,eam.status order by eam.name";
		return dao.query(sql);
	}
    /**
     * 查询待生产工单总数
     * @return
     */
    public List<Map<String, Object>> getDJobCodeList(){
        String sql = "select ef.name fzjcode,count(job.code) djobcode  " +
                "    from eam_facilities ef  " +
                "    left join emeso_job_order_exe exe on ef.eam_facilities_id = exe.eam_facilities_id  " +
                "    left join emeso_job_order job on job.emeso_job_order_id = exe.emeso_job_order_id  " +
                "    where ef.facility_type='FZJ' and job.status='CREATE'  " +
                "    GROUP BY ef.name ORDER BY ef.name";
        return dao.query(sql);
    }
    /**
     * 查询线边库各纱线库存数
     * @return
     */
    public List<Map<String, Object>> getProductKcQty(){
        String sql = "select mp.m_product_id,mp.name pname,eam.name fzjcode,eam.eam_facilities_id,count(io.qty) qty " +
                "from m_warehouse w,eam_facilities eam,wms_storage_in_out io,wms_storage_batch sb,m_product mp " +
                "where w.eam_facilities_id = eam.eam_facilities_id " +
                "and io.m_warehouse_id = w.m_warehouse_id " +
                "and io.wms_storage_batch_id = sb.wms_storage_batch_id " +
                "and sb.m_product_id = mp.m_product_id " +
                "GROUP BY mp.m_product_id,eam.name,eam.eam_facilities_id ORDER BY mp.m_product_id";
        return dao.query(sql);
    }

}
